--
-- Inserts static time values from earliest date used and 10 years into
-- the future with 1 minute intervals
--
declare
  oneMinute interval day To second := to_dsinterval('000 00:01:00');
  firstDate date;
  loopCount integer;
  lastDate date := add_months(sysdate, 12 * 10);
  season Dim_Time.season%type;
  seasonCalculation integer;
begin
  select min(datecol) 
    into firstDate 
    from 
        (
          select LAUNCHTIME as datecol 
              from TAFLIGHTSVEJLE 
          union 
          select LAUNCHTIME as datecol 
              from TAFLIGHTSSG70
          union
            select LANDINGTIME as datecol 
              from TAFLIGHTSVEJLE
          union
            select LANDINGTIME as datecol 
              from TAFLIGHTSSG70
        );
              
  select (lastDate - firstDate) * 1440 into loopCount from DUAL;
  
  for i in 1..loopCount loop
  
    select TO_CHAR(firstDate,'MM') 
      into seasonCalculation 
      from dual;
    
    IF seasonCalculation > 11 and seasonCalculation < 3  THEN
      season:='winter';
    ELSIF seasonCalculation > 2 and seasonCalculation < 6  THEN
      season:='spring';
    ELSIF seasonCalculation > 5 and seasonCalculation < 9  THEN
      season:='summer';
    ELSE
     season:='autumn';
    END IF;
    
    Insert into DIM_TIME 
      values 
      (
          time_seq.nextval
        , firstDate
        , (select to_char(firstDate,'D') from dual) 
        , (select to_char(firstDate,'DD') from dual)
        , (select to_char(firstDate,'W') from dual)
        , (select to_char(firstDate,'MM') from dual) 
        , (select to_char(firstDate,'HH') from dual)
        , (select to_char(firstDate,'MI') from dual)
        , (select to_char(firstDate,'YYYY') from dual)
        , season
      );
    firstDate := firstDate + oneMinute;
  end loop;
end;
/

commit;
